﻿--use PolePosition
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_VendorInspVisitXMissionStructurePole_SelectForGrid')
	DROP PROCEDURE up_VendorInspVisitXMissionStructurePole_SelectForGrid

GO

CREATE PROCEDURE [dbo].[up_VendorInspVisitXMissionStructurePole_SelectForGrid]
	 @Vendor_ProjectFK int 
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
	
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize


DECLARE @table table
(
 	 RowNumber int
	,TotalCount int
	,Vendor_InspVisit_XMissionStructure_PoleId int
	,Latitude float
	,Longitude float
	,[Year] int
	,LVisit datetime
	,LInspector varchar(100)
	,Number nvarchar(100)
	,AcquisitionDate datetime
	,TreatmentFk int
	,ClassFK int
	,SpecieFk int
	,TopTypeFk int
	,Length int
	,InspectorFk int
	,CompanyFk int
	,MapFk int
	,Vendor_InspVisit_XMissionStructureFk int
	,Vendor_ProjectFK int
	,Vendor_ContactFK int
	,SectionFk int
	,Utility_Asset_XMissionStructures_PoleFk int
	,[FriendlyName] varchar(255)
	,Vendor_InspVisit_XmissionStructures_Inspection_TypeFk int
	,Vendor_InspVisit_XmissionStructures_ResultTypeFk int
	,bElectrical bit
	,Comments ntext
	,TopTypeDisplay varchar(MAX)
	,NoteDisplay varchar(MAX)
	,Vendor_Visit_TypeFK int
	,VendorName varchar(50)
	,VendorContactName varchar(50)
	,GuardsQuantity int
	
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Vendor_InspVisit_XMissionStructure_PoleId]
	,[Latitude]
	,[Longitude]
	,[Year]
	,[LVisit]
	,[LInspector]
	,[Number]
	,[AcquisitionDate]
	,[TreatmentFk]
	,[ClassFK]
	,[SpecieFk]
	,[TopTypeFk]
	,[Length]
	,[InspectorFk]
	,[CompanyFk]
	,[MapFk]
	,[Vendor_InspVisit_XMissionStructureFk]
	,[Vendor_ProjectFK]
	,[Vendor_ContactFK]
	,[SectionFk]
	,[Utility_Asset_XMissionStructures_PoleFk]
	,[FriendlyName]
	,Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
	,Vendor_InspVisit_XmissionStructures_ResultTypeFk
	,bElectrical
	,Comments
	,TopTypeDisplay
	,NoteDisplay
	,Vendor_Visit_TypeFK
	,VendorName
	,VendorContactName
	,GuardsQuantity
)
SELECT 
*
FROM 
(
	SELECT 
		 ROW_NUMBER() OVER (ORDER BY Pole.Vendor_InspVisit_XMissionStructure_PoleId) AS RowNumber
		,COUNT(Pole.Vendor_InspVisit_XMissionStructure_PoleId) OVER (PARTITION BY NULL) AS TotalCount
		,Pole.[Vendor_InspVisit_XMissionStructure_PoleId]
		,Pole.[Latitude]
		,Pole.[Longitude]
		,Pole.[Year]
		,Pole.[LVisit] AS LVisit
		,Pole.[LInspector]
		,Pole.[Number]
		,Pole.[AcquisitionDate]
		,Pole.[TreatmentFk]
		,Pole.[ClassFK]
		,Pole.[SpecieFk]
		,Pole.[TopTypeFk]
		,Pole.[Length]
		,Pole.[InspectorFk]
		,Pole.[CompanyFk]
		,Pole.[MapFk]
		,Pole.[Vendor_InspVisit_XMissionStructureFk]
		,Pole.[Vendor_ProjectFK]
		,Pole.[Vendor_ContactFK]
		,Pole.[SectionFk]
		,Pole.[Utility_Asset_XMissionStructures_PoleFk]
		,Pole.[FriendlyName]
		,Result.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
		,Result.Vendor_InspVisit_XmissionStructures_ResultTypeFk
		,Result.bElectrical
		,Result.Comments
		--,Notes.Vendor_InspVisit_XmissionStructures_NoteFk
		,dbo.fn_VendorXmissionInspection_GetTopTypeString(Pole.Vendor_InspVisit_XMissionStructure_PoleId, 0) AS TopTypeDisplay
		,dbo.fn_VendorXmissionInspection_GetNoteString(Result.Vendor_InspVisit_XmissionStructures_ResultId) AS NoteDisplay
		,Pole.Vendor_Visit_TypeFK
		,Vendors.Name AS VendorName	
		,Contact.Name AS VendorContactName
		,Pole.GuardsQuantity
	FROM v_tb_Vendor_InspVisit_XmissionStructures_Poles Pole
	LEFT JOIN v_tb_Vendor_InspVisit_XMissionStructures Structure 
		ON Pole.Vendor_InspVisit_XMissionStructureFk = Structure.Vendor_InspVisit_XMissionStructureId
	LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Results Result
		ON Result.Vendor_InspVisit_XMissionStructure_PoleFk = Pole.Vendor_InspVisit_XMissionStructure_PoleId
--	LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_ResultsXNotes Notes
--		ON Notes.Vendor_InspVisit_XmissionStructures_ResultFk = Result.Vendor_InspVisit_XmissionStructures_ResultId
	LEFT JOIN tb_Vendor_Contacts Contact
		ON Contact.Vendor_ContactId = Pole.Vendor_ContactFK
	LEFT JOIN tb_Vendors Vendors 
		ON Vendors.VendorId = Contact.VendorFK
		
	WHERE
		Pole.Vendor_ProjectFK = @Vendor_ProjectFK
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber

-- primary select
select * from @table


-- VendorInspVisitXmissionStructuresImageCollection select

-- select
--	v_tb_Vendor_InspVisit_XmissionStructures_Images.* 
-- from 
-- 	v_tb_Vendor_InspVisit_XmissionStructures_Images 
-- 	JOIN @table x ON v_tb_Vendor_InspVisit_XmissionStructures_Images.Vendor_InspVisit_XMissionStructure_PoleFk = x.Vendor_InspVisit_XMissionStructure_PoleId
	

-- VendorInspVisitXmissionStructuresResultCollection select

select
 	v_tb_Vendor_InspVisit_XmissionStructures_Results.* 
from 
 	v_tb_Vendor_InspVisit_XmissionStructures_Results 
 	JOIN @table x ON v_tb_Vendor_InspVisit_XmissionStructures_Results.Vendor_InspVisit_XMissionStructure_PoleFk = x.Vendor_InspVisit_XMissionStructure_PoleId


